﻿using LogForParameter.IRepository;
using LogForParameter.IRepository.UnitOfWork;
using LogForParameter.Model;
using LogForParameter.Repository.Base;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using ViewModel;

namespace LogForParameter.Repository
{
    /// <summary>
    /// ORDERPATROL_APILOGRepository
    /// </summary>
    public class ORDERPATROL_APILOGRepository : BaseRepository<ORDERPATROL_APILOG>, IORDERPATROL_APILOGRepository
    {
        public ORDERPATROL_APILOGRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {

        }
        #region 方法实例测试
        /// <summary>
        /// 基础查询
        /// </summary>
        /// <returns></returns>
        public async Task<string> BaseQuaryTest()
        {
            //本项目目前使用的是5.1.2.6 :这里测试的时候必须要提醒一下的：使用left join的时候千万不要忘记加WHERE子句，也不要妄想把where子句和join  的 on 条件放到一起，因为这样条件是无法生效的，导致全表查询。LeftJoin(rightQuery, (mo, mod) => mo.ORDER_SN == mod.ORDER_SN)也就是这句话里面别妄想主表条件生效哈，别忘记（忘记写是小丑）。
            string msg = "ok";
            //1.动态or查询结合多条件查询
            var exp = Expressionable.Create<ORDERPATROL_APILOG>();
            exp.OrIF(1 == 1, m => m.ID <= 20);
            exp.Or(m => m.ID > 20);
            exp.And(m => m.TYPE == 1);//and 自动在之前的表达式外层加上一个括号
            exp.Or(m => m.TYPE == 0 && m.ERRORMSG.Contains("string1"));
            //var sqlv = Db.Queryable<ORDERPATROL_APILOG>().Where(exp.ToExpression());
            var list = await Db.Queryable<ORDERPATROL_APILOG>().Where(exp.ToExpression()).Select(m => new
            {
                m.ID,
                m.TYPE,
                m.ENTERTIME,
                m.HOSID
            }).ToListAsync();
            //2.指定条数查询
            /*主键查询*/
            var pk1 = Db.Queryable<ORDERPATROL_APILOG>().InSingle(20)?.ENTERTIME;
            var pk2 = Db.Queryable<ORDERPATROL_APILOG>().Single(m => m.ID == 19)?.ENTERTIME;
            /*多主键查询(注意主键查询必须严格赋值所有主键,否则将会失去目标)*/
            var getall = Db.Queryable<PUBLIC_USERROLE>().WhereClassByPrimaryKey(new PUBLIC_USERROLE() { HOSID = "1", JOBNUMBER = "2017060030", ROLEID = "1" }).ToList();//FIRST等于firstordefault()
            List<PUBLIC_USERROLE> pus = new List<PUBLIC_USERROLE> { new PUBLIC_USERROLE { JOBNUMBER = "2018060033", HOSID = "1" }, new PUBLIC_USERROLE { JOBNUMBER = "2017060030", HOSID = "1" } };
            /*支持集合查询*/
            var getalls = Db.Queryable<PUBLIC_USERROLE>().WhereClassByPrimaryKey(pus).OrderBy(m => m.JOBNUMBER, OrderByType.Desc).Take(5).ToList();//take(5)取前5条
            var getany = Db.Queryable<PUBLIC_USERROLE>().WhereClassByPrimaryKey(pus).OrderBy(m => m.JOBNUMBER, OrderByType.Desc).Any();
            //db.Queryable<Student>().Any(it=>it.Id>11) //上面语法的简化
            var getcount = Db.Queryable<PUBLIC_USERROLE>().WhereClassByPrimaryKey(pus).OrderBy(m => m.JOBNUMBER, OrderByType.Desc).Count();
            //设置新表名,自行研究.
            //3.in等函数应用
            decimal[] allIds = new decimal[] { 15, 16, 19 };
            var getins = Db.Queryable<ORDERPATROL_APILOG>().Where(m => allIds.Contains(m.ID)).ToList();
            /*多字段*/
            var expMultiIn = Expressionable.Create<PUBLIC_USERROLE>();
            foreach (var item in pus)
            {
                expMultiIn.Or(m => m.JOBNUMBER == item.JOBNUMBER && m.HOSID == item.HOSID);//条件可以自行控制
            }
            var getmultiin = Db.Queryable<PUBLIC_USERROLE>().Where(expMultiIn.ToExpression()).ToList();
            /*最大值函数*/
            //var list = db.Queryable()
            //.GroupBy(it => new { it.Id, it.Name })
            //.Having(it => SqlFunc.AggregateAvg(it.Id) > 0)//不是聚合函数用Where就可以了
            //.Select(it => new { idAvg = SqlFunc.AggregateAvg(it.Id), name = it.Name })
            //.ToList();
            //或者使用db.Queryable<Order>().MaxAsync(it=>it.Id);
            /*过滤某个字段*/
            var getIgnore = Db.Queryable<MEDICAL_ORDER>().IgnoreColumns(m => m.ORDER_NAME).Where(m => m.ORDER_TYPE == "medicine" && m.PATIENT_SN == "128132").ToList();
            //开始联查过滤
            var leftQuary = Db.Queryable<MEDICAL_ORDER>().IgnoreColumns(m => m.ORDER_NAME).Where(m => m.ORDER_TYPE == "medicine" && m.PATIENT_SN == "128132");
            var leftJoin = Db.Queryable(leftQuary).LeftJoin<MEDICAL_ORDER_DRUG>((mo, mod) => mo.ORDER_SN == mod.ORDER_SN).Select(mo => mo).ToList();
            //切面监控放在需要监控的sql前面，在执行时即可获取
            Db.Aop.OnLogExecuting = (sql, data) =>
            {
                var sql1 = sql;
            };
            //非主表
            var rightQuery = Db.Queryable<MEDICAL_ORDER_DRUG>().IgnoreColumns(m => m.PARENT_ORDER_SN);
            var right = Db.Queryable(leftQuary).LeftJoin(rightQuery, (mo, mod) => mo.ORDER_SN == mod.ORDER_SN).Select((mo, mod) => new { mod.ROUTE_NAME, mo.ORDER_CONTENT }).ToList();
            /*3.分页查询见基类BaseRepository{
             * 官方提示：
             * 获取row_indx 
             * 只支持sqlserver或oracle
             * [SugarColumn(IsIgnore=true)]//模型需要加上
                public int RowIndex{get;set;} //行号 序号
                其他数据库可以：
              int i = 1;
            var allindexs = Db.Queryable(leftQuary).Mapper((mo, cache) =>
            {
            it.num= i;//有分页的话需要计算一下 (pageindex-1)*pagesize+i
                i++; 
            }).ToList();
            //开窗口函数实现
            //index  =  SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc ");
            Count为什么返回不是long，而是int
            答： 1亿数据Count就要1分钟，别说int.max的20亿数据了，所以超过1000万以上的查询就不建议查询count，因为count的时间比查询数据的时间更长
             */
            RefAsync<int> totalCount = 0;
            var allindexs = Db.Queryable(leftQuary).ToPageListAsync(1, 20, totalCount);
            //4.分组查询去重 新版本支持了分组带函数  .GroupBy(it => new { it.Id, name = SqlFunc.ToString(it.Name) }
            var groupby = Db.Queryable(leftQuary).LeftJoin(rightQuery, (mo, mod) => mo.ORDER_SN == mod.ORDER_SN && mod.ORDER_SN != null)
                .GroupBy((mo, mod) => new { mo.ORDER_CODE, mo.CREATE_TIME })
                .Having((mo, mod) => SqlFunc.AggregateCount(mo.ORDER_SN) > 0)//不是聚合函数用Where就可以了
                .Distinct().Select((mo, mod) => new { mo.ORDER_CODE, mo.CREATE_TIME, num = SqlFunc.AggregateSum(mod.DRUG_NUMBER) }).ToList();
            //5.表达式排序
            //注意：如果存在Select一定要扔在select前面，不然 mo mod别名拿不到
            //动态排序功能

            var orderbyPatientSn = Db.EntityMaintenance.GetDbColumnName<MEDICAL_ORDER>("PATIENT_SN");//说是防注入
            //OrderBy(mo => mo.CREATE_TIME, OrderByType.Desc).OrderBy((mo, mod) => new { mo.ORDER_TYPE, mo.PARENT_ORDER_SN }).OrderBy($"{orderbyPatientSn} asc")
            var sortTemp = Db.Queryable<MEDICAL_ORDER, MEDICAL_ORDER_DRUG>((mo, mod) => new JoinQueryInfos(JoinType.Inner, mo.ORDER_SN == mod.ORDER_SN && mo.CREATE_TIME >= DateTime.Now.AddDays(-1)))
                .Select((mo, mod) => new { mo.ORDER_SN, mo.PATIENT_SN, mo.ORDER_CONTENT, mo.ORDER_TYPE, mo.PARENT_ORDER_SN, mo.CREATE_TIME, mo.START_TIME, mod.DOSE_UNIT })
                .MergeTable()//说是将结果集变成单表MergeTable来取消别名mo,mod的语法限制（注意：使用 MergeTable不能有 Take Skip OrderBy PageToList 等操作,你可以在Mergetable之后操作）
                .Where(it => it.CREATE_TIME > DateTime.Now.AddDays(-0.5))
                .Take(10).OrderBy(it => SqlFunc.GetRandom())//随机排序
                .OrderByIF(true, it => it.START_TIME)//条件排序
                .ToList();
            DateTime start = Convert.ToDateTime("2022/11/17");
            DateTime end = Convert.ToDateTime("2022/11/18");
            #region 6联查 
            //内联用InnerJoin，FullJoin需要高版本才支持用法自行确认
            //5.0.4.2酱紫
            var left1 = Db.Queryable<MEDICAL_ORDER>().LeftJoin<MEDICAL_ORDER_DRUG>((mo, mod) => mo.ORDER_SN == mod.ORDER_SN).Where((mo, mod) => mo.CREATE_TIME >= start && mo.CREATE_TIME < end)
                .Select((mo, mod) => new { mo.ORDER_SN, mo.CREATE_TIME, mod.DOSE_UNIT }).ToList();
            //5.0.4.3酱紫
            var left2 = Db.Queryable<MEDICAL_ORDER>().LeftJoin<MEDICAL_ORDER_DRUG>((mo, mod) => mo.ORDER_SN == mod.ORDER_SN).Where((mo, mod) => mo.CREATE_TIME >= start && mo.CREATE_TIME < end)
               .Select(mo => mo).ToList();
            //5.0.4.3套娃使用
            var left11 = Db.Queryable<MEDICAL_ORDER>();
            var left12 = Db.Queryable(left11).LeftJoin<MEDICAL_ORDER_DRUG>((mo, mod) => mo.ORDER_SN == mod.ORDER_SN).Where((mo, mod) => mo.CREATE_TIME >= start && mo.CREATE_TIME < end).Select(mo => mo).ToList();
            /*
             更多套娃用法看嵌套查询：

            https://www.donet5.com/Home/Doc?typeId=2354

            表和内存集合查询

            https://www.donet5.com/Home/Doc?typeId=2315
             */
            //语法糖2
            var join1 = Db.Queryable<MEDICAL_ORDER, MEDICAL_ORDER_DRUG>((mo, mod) => new JoinQueryInfos(
                JoinType.Left, mo.ORDER_SN == mod.ORDER_SN
                )).Where((mo, mod) => mo.CREATE_TIME >= start && mo.CREATE_TIME < end).Select((mo, mod) => mod).ToList();
            //语法糖3
            //如果全部inner join 可以用这种方式直接连表
            var join2 = Db.Queryable<MEDICAL_ORDER, MEDICAL_ORDER_DRUG>((mo, mod) => mo.ORDER_SN == mod.ORDER_SN).Where((mo, mod) => mo.CREATE_TIME >= start && mo.CREATE_TIME < end)
                .Select<dynamic>((mo, mod) => new
                {
                    ORDER_SN = mo.ORDER_SN.SelectAll(),//st.*  (SelectAll建议只用一张表，不然查询列会有重名)
                    DoseUnit = mod.DOSE_UNIT//Name as SchoolName
                }).ToList();  //自动主表赋值所有字段

            var join3 = Db.Queryable<MEDICAL_ORDER, MEDICAL_ORDER_DRUG>((mo, mod) => mo.ORDER_SN == mod.ORDER_SN).Where((mo, mod) => mo.CREATE_TIME >= start && mo.CREATE_TIME < end)
              .Select((mo, mod) => new PublicTest
              {
                  Order_Sn = mo.ORDER_SN.SelectAll(),//st.*  (SelectAll建议只用一张表，不然查询列会有重名)
                  MEDICAL_ORDER_DRUGDose_Unit = mod.DOSE_UNIT//Name as SchoolName
              }).ToList();//主表自动赋值，子表指定赋值

            //接下来试下自动映射viewmodel必须遵循一定原则（请转到对应的vm里面查看）
            var vm1 = Db.Queryable<MEDICAL_ORDER, MEDICAL_ORDER_DRUG>((mo, mod) => mo.ORDER_SN == mod.ORDER_SN).Where((mo, mod) => mo.CREATE_TIME >= start && mo.CREATE_TIME < end)
              .Select<PublicTest>().ToList();
            /*
               1.ViewOrder必须每个列都能匹配到字段，否则就无法按规则匹配，保证每个列都正确
             
         2.高并发功能不建议使用，手写的性能肯定高于自动映射
            更多select  https://www.donet5.com/Home/Doc?typeId=1186
             */
            //语法糖4:导航属性 ： [Navigate(NavigateType.OneToOne, nameof(SchoolId))]类似于外键指向StudentA.SchoolId=SchoolA.SchoolId，来得到StudentA.SchoolA.SchoolName == "北大"这样的偷懒条件。这里不推荐使用，我不是懒人(:

            /*
             public class StudentA
 {
     [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
     public int StudentId { get; set; }
     public string Name { get; set; }
     public int SchoolId { get; set; }
     [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是StudentA类里面的
     public SchoolA SchoolA { get; set; } //不能赋值只能是null

 }
 public class SchoolA
 {
     [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
     public int SchoolId { get; set; }
     public string SchoolName { get; set; }
 }
             */

            #endregion
            #region 导航查询暂不做尝试自行查看官网

            #endregion
            #region 子查询
            //First: SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)        
            //max:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Max(s => s.Id)    
            //min:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Min(s => s.Id)
            //avg:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Avg(s => s.Id)
            //count:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Count()
            //sum:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Sum(s => s.num)

            //5.0.7.6支持去重后汇总数量
            //SqlFunc.Subqueryable<School>().Where(s => z.Id == st.Id).Select(s=>SqlFunc.AggregateDistinctCount(s.Name))

            //5.1.1-preview14 新功能
            //逗号分割列: SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")
            //子查询 where中使用子查询，指定默认第一个学校id的学生;
            //            var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left, st.Id == sc.Id))
            //.Where(st => st.Id == SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id))
            //.ToList();
            /*使用子查询，指定默认匹配的第一个学校名称
             * var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id ))
.Select(st => new{
          name = st.Name,
          schoolName = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.schoolName)
       }).ToList();
            */
            /*
             2IN、NOT IN
            2.1 exists:较in扩展性更强，支持多个字段（简单的说InNeng实现的Exists都能实现）
            id在school里面存在的学生：
            var getAll7 = db.Queryable<Student>()
.Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any()).ToList();
             //id不在school里面表里面的学生：
            var getAll8 = db.Queryable<Student>().Where(it =>
SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).NotAny()).ToList();
             2.2 In和 Not In (5.0.7.5) 在有GroupBy的情况下==自动转成 in :查询订单中Id
            db.Queryable<Order>()
.Where(it=>it.Id==SqlFunc.Subqueryable<Custom>().GroupBy(z=>z.OrderId).Select(z=>z.OrderId)).ToList()
             */
            // 4.toList()集合对象
            //方式1：（5.1.3.36+）直接在select中进行ToList:
            //性能：大于循环，大部分情况性能小于ThenMapper方式和导航,但写法简单性能也说的过去
            var tolist1 = Db.Queryable<PATIENT_INPATIENTINFO>().Where(m => m.PATIENTSN == "128412").Select(m => new
            {
                patientsn = m.PATIENTSN,
                patientname = m.PATIENTNAME,
                admissionregistertime = m.ADMISSIONREGISTERTIME,
                medicalorders = SqlFunc.Subqueryable<MEDICAL_ORDER>().Where(mo => mo.PATIENT_SN == m.PATIENTSN).ToList()
            }).ToList();
            //方式2：性能、功能、跨库:性能较好不会生成垃圾sql，在现有结果进行一对多操作：
            //例子科室树
            var depRoot = Db.Queryable<PUBLIC_DEPARTMENT>().Where(m => m.PARENTDEPARTMENTID == "0").ToList();
            //使用ThenMapper实现子查询一对多操作
            //填充子对象 
            //原理： 数据库一次读取 In (list[0].id,list[1].id,list[2].id...) 内存分配到对应Child
            //所以无论集合多少条记录  ThenMapper 只会操作一次库  
            Db.ThenMapper(depRoot, item =>
            {
                //用例1：简单填充  注意(public_departments使用了导航属性)
                //参数解释：子表的ParentId和主表的Id 进行关联查询（有Select写到SetContext前面）
                item.PUBLIC_DEPARTMENTS = Db.Queryable<PUBLIC_DEPARTMENT>().SetContext(c => c.PARENTDEPARTMENTID, () => item.DEPARTMENTID, item).ToList();
                //用例2：可以多属性
                //item.Child=xxxx
                //item.Parent=xxxx
                //用例3：类型转换类转成DTO （注意：Select中的关联字段要赋值）
                //item.Child = db.Queryable<Tree>()
                //         .Select(it=>new ViewTree(){ParentId=x.ParentId,Id=i.Id,Name=it.Name }) 
                // 
                //用例4：联表填充
                //item.Child=db.Queryable<Tree>()
                //.LeftJoin<Tree>((i,y)=>i.ParentId==y.Id)
                //.Select((i,y)=>new viewmodel(){ Parentid=i.Parentid..........})
                //.SetContext(x => x.ParentId, () => item.Id, item).ToList();
            });
            //异步:
            var depRootAsynchronous = await Db.Queryable<PUBLIC_DEPARTMENT>().Where(m => m.PARENTDEPARTMENTID == "0").ToListAsync();
            await Db.ThenMapperAsync(depRootAsynchronous, async item =>
            {
                item.PUBLIC_DEPARTMENTS = await Db.Queryable<PUBLIC_DEPARTMENT>().SetContextAsync(x => x.PARENTDEPARTMENTID, () => item.DEPARTMENTID, item);
            });
            //无限层级
            var treeRoot = Db.Queryable<PUBLIC_DEPARTMENT>().Where(m => m.PARENTDEPARTMENTID == "0").ToList();
            //第一层
            Db.ThenMapper(treeRoot, item =>
            {
                item.PUBLIC_DEPARTMENTS = Db.Queryable<PUBLIC_DEPARTMENT>().SetContext(x => x.PARENTDEPARTMENTID, () => item.DEPARTMENTID, item).ToList();
            });
            //第二层
            Db.ThenMapper(treeRoot.SelectMany(m => m.PUBLIC_DEPARTMENTS), item =>
            {
                item.PUBLIC_DEPARTMENTS = Db.Queryable<PUBLIC_DEPARTMENT>().SetContext(x => x.PARENTDEPARTMENTID, () => item.DEPARTMENTID, item).ToList();
            });
            //跨库 具体需要自己实现
            //var mydb = Db.GetConnection(1);
            //6.别名： SqlFunc.Subqueryable<A>().AS("A01")  //这样查询的就是 A01表
            //7.7、字符串拼接 5.1.1 - preview14 逗号拼接
            var strJoin = Db.Queryable<PATIENT_INPATIENTINFO>().Where(m => m.PATIENTSN == "128412").Select(m => new
            {
                patientsn = m.PATIENTSN,
                patientname = m.PATIENTNAME,
                admissionregistertime = m.ADMISSIONREGISTERTIME,
                medicalorderContents = SqlFunc.Subqueryable<MEDICAL_ORDER>().Where(mo => mo.PATIENT_SN == m.PATIENTSN).SelectStringJoin(z => z.ORDER_CONTENT, ","),
                depname = SqlFunc.Subqueryable<PUBLIC_DEPARTMENT>().Where(p => p.DEPARTMENTID == m.CURRENTDEPARTMENTID).Select(p => p.DEPARTMENTNAME),
            }).ToList();
            #endregion
            #region 嵌套查询

            #endregion
            return msg;
        }
        #endregion
    }
}